<?php defined('SYSPATH') or die('No direct script access.');

class Controller_Advanced extends Controller_Common {

	public $template='layout/advanced';
	protected $key;

	public static function getTotalResultsItem() {

	}

	public function action_index() {
		$this->template->header=View::factory('pieces/header');

		$defaults = array( // массив со значениями по умолчанию для кнопок. "Суточные номера" во вьюшке
			'fromCountry' => 'Откуда',
			'fromCity' => 'Откуда',
			'where' => 'Куда', 
			'aviacompany' => 'Авиакомпания', 
			'when' => 'Когда', 
			'nights' => 'На сколько ночей', 
			'package' => 'Выбрать тур-пакет', 
			'hotel' => 'Отель', 
			'stars' => '',
			'roomType' => 'Тип номера отеля', 
			'pitanie' => 'Тип питания', 
			'adults' => 'Кол-во взрослых', 
			'childrens' => 'Количество человек',
			'childrenAge_1' => '',
			'childrenAge_2' => '',
			'additional' => '',
			'currency' => 'тенге',
			'priceFrom' => '',
			'priceTo' => '',
		);

		$single = array('when', 'currency', 'priceFrom', 'priceTo', 'additional', 'childrenAge_1', 'childrenAge_2', 'adults', 'childrens', 'stars'); // кнопки в которых и имя и значение одинаковы (дата например или количество чего либо)
		$double = array('fromCountry', 'fromCity', 'where', 'aviacompany', 'package', 'hotel', 'nights', 'roomType', 'pitanie'); // кнопки в которых имя и значения отличаются друг от друга
		$params = array();

		foreach ($defaults as $key => $value) {
			if (empty($_GET[$key])) { // если ГЕТ элемент пустой
				if (in_array($key, $double)) { // если значение входит в массив с "двойными параметрами"
					$params[$key]['name'] = $value; // то присваиваем ему значение по умолчанию
					$params[$key]['value'] = '';
				}
				else { // иначе (если значение не входит в массив)
					$params[$key]['name'] = $value;
					$params[$key]['value'] = '';
				}

				$params[$key]['display'] = 'style="display: none"';
			}
			else { // иначе (если ГЕТ не пустой)
				if (in_array($key, $double)) { // если значение входит в массив с "двойными параметрами"
					$exploded = explode('!', $_GET[$key]);
					$params[$key]['name'] = $exploded[0]; // то присваиваем ему своё же значение
					$params[$key]['value'] = $exploded[1];
				}
				else { // иначе
					$params[$key]['name'] = $_GET[$key];
					$params[$key]['value'] = $_GET[$key];
				}

				$params[$key]['display'] = '';
			}
		}

		if (!empty($_COOKIE['myRegionCountry'])) { // если есть куки региона, то записываем дефолтные значения из кук
			$cookie = explode('!', $_COOKIE['myRegionCountry']);
			$params['fromCountry']['name'] = $cookie[0];
			$params['fromCountry']['value'] = $cookie[1];
			$params['fromCountry']['display'] = '';
			$fromName = $cookie[0];
			$fromValue = $cookie[1];
		}
		else { // иначе как обычно
			$fromName = $params['fromCountry']['name'];
			$fromValue = $params['fromCountry']['value'];
		}

		if (!empty($_COOKIE['myRegionCity'])) {
			$cookie = explode('!', $_COOKIE['myRegionCity']);
			$params['fromCity']['name'] = $cookie[0];
			$params['fromCity']['value'] = $cookie[1];
			$params['fromCity']['display'] = '';
			$fromName = $cookie[0];
			$fromValue = $cookie[1];
		}
		else {
			if (!empty($_COOKIE['myRegionCountry'])) {
				$cookie = explode('!', $_COOKIE['myRegionCountry']);
				$fromName = $cookie[0];
				$fromValue = $cookie[1];
			}
			else {
				$fromName = $params['fromCity']['name'];
				$fromValue = $params['fromCity']['value'];
			}
		}
		$search = $this->search($params);
		$this->template->content=View::factory('pages/advanced', array(
			'params' => $params, 
			'fromName' => $fromName, 
			'fromValue' => $fromValue,
			'search' => $search['result'], // поиск
			'pagination' => Pagination::factory(array(
				'current_page'      => array('source' => 'query_string', 'key' => 'page'),
            	'view' => 'pagination/floating',
                'total_items' => $search['count'],
                'items_per_page' => 10,

            ))
		));

		$menu = Model::factory('menu')->getMenu();
        $this->template->footer=View::factory('pieces/footer', array(
            'menu' => $menu
        ));
	}
public function search($data)
	{
		if(empty($data['fromCity']['value']) || empty($data['when']['value']) || empty($data['when']['value']) || empty($data['nights']['value']))
			return false;
		$data['date'] = strtotime($data['when']['value']);
		$flights_childrens_sum = '';
		$start_date = date('Y-m-d', $data['date']);
		$end = new DateTime($start_date);
		$end->modify('+'.$data['nights']['value'].' day');
		$end = strtotime($end->format('Y-m-d'));
		$parameters = array(
			':city_from' => $data['fromCity']['value'],
			':city_to' => $data['where']['value'],
			':date_start' => $data['date'],
			':date_end' => $end,
			':count_days' => $data['nights']['value'],
			':day_of_week' => '%,'.date('w', $data['date']).',%',
			':child_count' => 0,
		);

		if($data['pitanie']['value']!=''){
			$parameters[':food'] = $data['pitanie']['value'];
			$food_sql = ' AND hdates.food = :food';
		}else $food_sql = '';

		if($data['package']['value']!=''){
			$parameters[':package_id'] = $data['package']['value'];
			$package_id_sql = ' AND packages.id = :package_id';
		}else $package_id_sql = '';

		if($data['hotel']['value']!=''){
			$parameters[':hotel_id'] = $data['hotel']['value'];
			$hotel_id_sql = ' AND hotels.id=:hotel_id';	
		}else $hotel_id_sql = ''; 

		if($data['roomType']['value']!=''){
			$parameters[':hotel_type'] = $data['roomType']['value'];
			$hotel_type_sql = ' AND hdates.type=:hotel_type';	
		}else $hotel_type_sql = ''; 

		if($data['adults']['value']!=''){
			$parameters[':adult_count'] = $data['adults']['value'];
			$adult_count_sql = ' AND hdates.adult_count=:adult_count';	
		}else $adult_count_sql = ''; 

		$additional_childrens_sum = '';

		if(!empty($_GET['childrenAge_1'])){
			$parameters[':children_years_1'] = $_GET['childrenAge_1'];
			
			if(empty($_GET['childrenAge_2'])){
				$parameters[':child_count'] = 1;
				$flights_childrens_sum = '
				IfNull((SELECT `price` FROM `children_categories` WHERE `y_start` < :children_years_1 AND `y_end` >= :children_years_1 AND `table`="flights" AND `table_id`=f1.id), f1.price_adult) + 
				IfNull((SELECT `price` FROM `children_categories` WHERE `y_start` < :children_years_1 AND `y_end` >= :children_years_1 AND `table`="flights" AND `table_id`=f2.id), f2.price_adult) + 
				';
				$additional_childrens_sum = '
				+ (SELECT SUM(`price`) FROM children_categories WHERE `y_start` < :children_years_1 AND `y_end` >= :children_years_1 AND `table` = "additional" AND `table_id` IN (SELECT `id` FROM `additional` WHERE `parent`= :city_to))
				';
			}elseif(!empty($_GET['childrenAge_2'])){
				$parameters[':child_count'] = 2;
				$parameters[':children_years_2'] = $_GET['childrenAge_2'];
				$flights_childrens_sum = '
				IfNull((SELECT `price` FROM `children_categories` WHERE `y_start` < :children_years_1 AND `y_end` >= :children_years_1 AND `table`="flights" AND `table_id`=f1.id), f1.price_adult) + 
				IfNull((SELECT `price` FROM `children_categories` WHERE `y_start` < :children_years_2 AND `y_end` >= :children_years_2 AND `table`="flights" AND `table_id`=f1.id), f1.price_adult) + 
				IfNull((SELECT `price` FROM `children_categories` WHERE `y_start` < :children_years_1 AND `y_end` >= :children_years_1 AND `table`="flights" AND `table_id`=f2.id), f2.price_adult) + 
				IfNull((SELECT `price` FROM `children_categories` WHERE `y_start` < :children_years_2 AND `y_end` >= :children_years_2 AND `table`="flights" AND `table_id`=f2.id), f2.price_adult) + 
				';
				$additional_childrens_sum = '
				+ IfNull((SELECT `price` FROM children_categories WHERE `y_start` < :children_years_1 AND `y_end` >= :children_years_1 AND `table` = "additional" AND `table_id` IN (SELECT `id` FROM `additional` WHERE `parent`= :city_to)), 0)
				+ IfNull((SELECT `price` FROM children_categories WHERE `y_start` < :children_years_2 AND `y_end` >= :children_years_2 AND `table` = "additional" AND `table_id` IN (SELECT `id` FROM `additional` WHERE `parent`= :city_to)), 0)
				';
			}
			
		}

		$child_count_sql = ' AND hdates.child_count=:child_count';

		if($data['priceFrom']['value']!=''){
			$parameters[':price_from'] = $data['priceFrom']['value'];
			$price_from_sql = ' AND sum >= :price_from';	
		}else $price_from_sql = ''; 

		if($data['priceTo']['value']!=''){
			$parameters[':price_to'] = $data['priceTo']['value'];
			$price_to_sql = ' AND sum <= :price_to';
		}else $price_to_sql = '';

/*(hdates.price * :count_days) + 
            */
		$query = '
		SELECT 
			pckg.package_id as package_id,
            pckg.package_title as package_title,
            pckg.package_description as package_description,
            pckg.hotel_id as hotel_id,
            pckg.hotel_title as hotel_title,
            pckg.hotel_description as hotel_description,
            hdates.type as room_type,
            hdates.adult_count as adult_count,
            hdates.child_count as child_count,
            hdates.food as food,
            hdates.start,
            hdates.end,
            f1.title AS aviacompany_out,
            f2.title AS aviacompany_back,
            f1.out_time as flight_out_time,
            f1.in_time as flight_back_time,
            ('.date('d.m', $end).') as back_date,
            pckg.package_commission as comission,

            ((hdates.price * :count_days) + 
            (f1.price_adult * adult_count) + 
            (f2.price_adult * adult_count) + 
            '.$flights_childrens_sum.'
            IfNull(pckg.additional_sum, 0) 
            '.$additional_childrens_sum.'
             ) as sum 
		FROM 
			(
				SELECT 
					packages.id as package_id, 
					packages.title as package_title,
					packages.description as package_description,
					packages.commission as package_commission,
					hotels.title as hotel_title, 
					hotels.description as hotel_description, 
					hotels.id as hotel_id,
					(SELECT SUM(`price`) FROM additional WHERE parent = :city_to) AS additional_sum 
				FROM 
					packages,hotels,package_hotels 
				WHERE 
					packages.parent=:city_to AND packages.id=package_hotels.package_id AND hotels.id = package_hotels.hotel_id'.$package_id_sql.$hotel_id_sql.'
			) pckg 
		LEFT JOIN hotel_placement_dates hdates ON (hdates.start<=:date_start AND hdates.end>=:date_end AND pckg.hotel_id = hdates.parent'.$hotel_type_sql.$adult_count_sql.$child_count_sql.$food_sql.')
		INNER JOIN (SELECT * FROM `flights` WHERE city_from=:city_from AND city_to=:city_to AND period_start<=:date_start AND period_end>=:date_end AND out_day LIKE :day_of_week) f1
		INNER JOIN (SELECT * FROM `flights` WHERE city_from=:city_from AND city_to=:city_to AND period_start<=:date_start AND period_end>=:date_end AND in_day LIKE :day_of_week) f2
		HAVING room_type IS NOT Null'.$price_from_sql.$price_to_sql.'
		ORDER BY sum ASC

		';

		$return_count = DB::query(Database::SELECT, $query)->parameters($parameters)->execute()->count();

		$max_on_page = 4;
		if(isset($_GET['page'])){$lim=($_GET['page']-1)*$max_on_page.' , '.$max_on_page;}else{$lim=$max_on_page;}
		$res = DB::query(Database::SELECT, $query.' LIMIT '.$lim)->parameters($parameters)->execute();
		//echo $res;
		
		return array('result' => $res, 'count' => $return_count);
	}

	public function get_aviacompanies($city_from, $city_to)
	{

	}
}